package com.example.calcite.demo;

import com.example.calcite.CalciteDataSource;
import com.example.calcite.csv.CsvSchema;
import com.example.calcite.csv.CsvTable;
import com.example.calcite.memory.MemorySchema;
import com.example.calcite.memory.MemoryTable;
import com.example.calcite.mysql.MySQLSchema;
import com.zaxxer.hikari.HikariConfig;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Properties;

public class CalciteDataSourceDemo {
    public static void main(String[] args) {
        // Configure and create data source
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:calcite:");
        config.setDriverClassName("org.apache.calcite.jdbc.Driver");
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(5);
        config.setIdleTimeout(300000);
        config.setConnectionTimeout(10000);
        config.setMaxLifetime(1800000);
        Properties info = new Properties();
        // 设置大小写不敏感
        info.setProperty("caseSensitive", "false");
        config.setDataSourceProperties(info);
        
        CalciteDataSource dataSource = new CalciteDataSource(config);
        
        try {
            // Register schemas
            MemorySchema memorySchema = new MemorySchema();
            CsvSchema csvSchema = new CsvSchema();
            
            dataSource.registerSchema("MEMORY", memorySchema);
            dataSource.registerSchema("CSV", csvSchema);
            
            // Register MySQL schema
            /*dataSource.registerSchema("MYSQL", new MySQLSchema(
                "jdbc:mysql://localhost:3306/hr",
                "root",
                "root"
            ));*/
            
            // Create tables
            MemoryTable departmentsTable = new MemoryTable(new String[]{"ID", "NAME", "LOCATION"});
            memorySchema.addTable("DEPARTMENTS", departmentsTable);
            
            // Create CSV table
            CsvTable employeesTable = new CsvTable(CalciteDataSourceDemo.class.getClassLoader().getResource("employees.csv").getPath());
            csvSchema.addTable("EMPLOYEES", employeesTable);
            
            // Insert data into memory table
            departmentsTable.insert(1, "Engineering", "New York");
            departmentsTable.insert(2, "Marketing", "Los Angeles");
            
            // Get connection and execute query
            try (Connection connection = dataSource.getConnection();
                 Statement statement = connection.createStatement()) {
                
                // 示例查询：从 MySQL 表和其他表联合查询
                ResultSet resultSet = statement.executeQuery(
                    "SELECT E.ID, E.NAME, E.AGE, E.SALARY, D.NAME AS DEPARTMENT, D.LOCATION " +
                           // ", " +
                    // "M.CITY AS CITY " +
                    "FROM CSV.EMPLOYEES E " +
                    "JOIN MEMORY.DEPARTMENTS D ON E.DEPARTMENT_ID = D.ID " +
                    //"JOIN MYSQL.EMPLOYEE_ADDRESS M ON E.ID = M.EMPLOYEE_ID " +
                    "WHERE E.AGE > 25 and D.NAME = 'asdfas'" +
                    "ORDER BY E.SALARY DESC");
                
                // Print results
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                
                // Print column headers
                for (int i = 1; i <= columnCount; i++) {
                    System.out.print(metaData.getColumnName(i) + "\t");
                }
                System.out.println();
                
                // Print data
                while (resultSet.next()) {
                    for (int i = 1; i <= columnCount; i++) {
                        System.out.print(resultSet.getString(i) + "\t");
                    }
                    System.out.println();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dataSource.close();
        }
    }
} 